package cn.sdnu.database.service;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.locks.ReentrantLock;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.sdnu.database.controller.DbController;
import cn.sdnu.database.utils.Utils;

/**
 * @author Master_Joe qiaoguangtong@aliyun.com
 */
public class Insert {
	private static Insert insert = null;
	private static ReentrantLock lock = new ReentrantLock();

	private Insert() {

	}

	public static Insert getInstance() {
		if (insert == null) {
			lock.lock();
			try {
				if (insert == null) {
					insert = new Insert();
				}
			} finally {
				lock.unlock();
			}
		}
		return insert;
	}

	/**
	 * 处理 SQL 语句
	 * 
	 * @param sql 要处理的SQL语句
	 */
	public void sqlHandle(String sql) {
		if (Use.getInstance().getDatabaseUsed() != null) {
			/*
			 * 1. 先判断有没有use数据库 2. 判断语句是否正确 3. 插入
			 */
			if (verifyInsertSQL(sql)) {
				insertValues(sql);
			} else {

			}
		} else {
			System.out.println("ERROR: No database selected");
		}
	}

	/**
	 * 插入存储数据
	 * 
	 * @param sql 根据SQL 获取我们的信息(插入的表名字， 列的名字);
	 * 
	 */
	private void insertValues(String sql) {
		sql = sql.replace("'", "");
		String tableNames = sql.substring(12);
		int spaceIndexInTableNames = tableNames.indexOf(" ");
		int parenthesisIndexInTableNames = tableNames.indexOf("(");
		String tableName = "";
		String[] insertValues = null;
		if (spaceIndexInTableNames < parenthesisIndexInTableNames) {
			tableName = tableNames.substring(0, spaceIndexInTableNames);
		} else {
			tableName = tableNames.substring(0, parenthesisIndexInTableNames);
		}

		if (Utils.getTables(Use.getInstance().getDatabaseUsed()).contains(tableName)) {
			List<String> inputColumns = new ArrayList<String>();
			List<String> inputValues = new ArrayList<String>();
			if (2 == Utils.countString(sql, '(')) {
				int parenthesisLeftIndex = sql.indexOf("(");
				int parenthesisRightIndex = sql.indexOf(")");
				String columnsInput = sql.substring(parenthesisLeftIndex + 1, parenthesisRightIndex);
				inputColumns = Arrays.asList(columnsInput.split(", "));
				int parenthesisLeftLastIndex = sql.lastIndexOf("(");
				int parenthesisRightLastIndex = sql.lastIndexOf(")");
				String values = sql.substring(parenthesisLeftLastIndex + 1, parenthesisRightLastIndex);
				insertValues = values.split(", ");
				inputValues = Arrays.asList(insertValues);
			} else {
				int parenthesisLeftLastIndex = sql.lastIndexOf("(");
				int parenthesisRightLastIndex = sql.lastIndexOf(")");
				String values = sql.substring(parenthesisLeftLastIndex + 1, parenthesisRightLastIndex);
				insertValues = values.split(", ");
				inputValues = Arrays.asList(insertValues);
			}

			List<String> columns = Utils.getColumns(tableName, Use.getInstance().getDatabaseUsed());

			Map<String, String> map = new LinkedHashMap<String, String>();
			// initialize the map
			if (columns != null && columns.size() > 0) {
				for (String str : columns) {
					map.put(str, DbController.NULL_VALUE);
				}
			}

			Set<String> keySet = map.keySet();

			for (int i = 0; i < inputColumns.size(); i++) {
				if (keySet.contains(inputColumns.get(i))) {
					map.replace(inputColumns.get(i), inputValues.get(i));
				}
			}

			if (inputColumns.size() == 0) {
				for (int i = 0; i < inputValues.size(); i++) {
					map.replace(columns.get(i), inputValues.get(i));
				}
			}

			StringBuilder insertLine = new StringBuilder();
			insertLine.append(DbController.HEADER_INFO);
			for (String string : keySet) {
				String string2 = map.get(string);
				insertLine.append(string2);
				insertLine.append(DbController.INTERVAL_INFO);
			}

			StringBuilder append = insertLine.append(DbController.END_INFO);
			String path = Utils.getTablePath(tableName, Use.getInstance().getDatabaseUsed());
			FileWriter fileWriter = null;

			try {
				fileWriter = new FileWriter(new File(path), true);
				fileWriter.write(append.toString());
				fileWriter.write("\n");
				System.out.println("Query OK, 1 row affected");
			} catch (Exception e) {
				System.err.println("System error, exit");
				System.exit(0);
			} finally {
				try {
					fileWriter.close();
				} catch (IOException e) {
					System.err.println("System error, exit");
					System.exit(0);
				}
			}
		} else {
			System.out.println(
					"ERROR : Table '" + Use.getInstance().getDatabaseUsed() + "." + tableName + "' doesn't exist");
		}
	}

	/**
	 * 验证我们的SQL是否正确
	 * 
	 * @param sql 输入的SQL
	 * @return {@code true} SQL语句正确, {@code false} SQL 语句不正确
	 */
	private boolean verifyInsertSQL(String sql) {
		String[] splits = sql.split("\\(|\\)");
		String[] split2 = splits[0].split(" ");
		String tableName = split2[2];// 当前表
		List<String> allTables = Utils.getTables(Use.getInstance().getDatabaseUsed());
		boolean exits = false;
		if (allTables.contains(tableName)) {
			exits = true;
		}
		if (!exits) {
			System.out.println(
					"Error: Table " + Use.getInstance().getDatabaseUsed() + "." + tableName + " is not existed!");
			return false;
		}
		String typePatten = "insert into \\w+[\\s]*";
		String typePatten2 = "insert into \\w+[\\s]*values[\\s]*";
		Pattern p = Pattern.compile(typePatten, Pattern.CASE_INSENSITIVE);
		Pattern p2 = Pattern.compile(typePatten2, Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(splits[0]);
		Matcher m2 = p2.matcher(splits[0]);
		if (m.matches()) {
			// 类似于insert into table(...) values(...
//			splits[1] = splits[1].replaceAll("\\s", "");
			String[] keys = splits[1].split(", ");// 字段名字
			for (String key : keys) {
				List<String> table_key = Utils.getColumns(tableName, Use.getInstance().getDatabaseUsed());
				exits = false;
				for (String str : table_key) {
					if (str.equals(key)) {
						exits = true;
						break;
					}
				}
				if (!exits) {
					System.out.println("Error: Field " + key + " is not existed!");
					return false;
				}
			}
			// 判断value是否正确
			typePatten = "[\\s]*values[\\s]*";
			p = Pattern.compile(typePatten, Pattern.CASE_INSENSITIVE);
			m = p.matcher(splits[2]);
			if (!m.matches()) {
				System.out.println("Error: You have some syntax error near the values, please try again");
			}
			// 判断输入内容
			String value = splits[3].replaceAll("\\s", "");
			String[] values = value.split(",");
			if (values.length != keys.length) {
				System.out.println("ERROR: Column count doesn't match value count at row 1");
				return false;
			}
			for (String string : values) {
				// 判断每个value
				String numberOnlyPattern = "^[0-9]*$";
				String stringPattern = "\\'.*\\'";
				String nullPattern = "null";
//				  null
				p = Pattern.compile(numberOnlyPattern, Pattern.CASE_INSENSITIVE);
				m = p.matcher(string);
				p2 = Pattern.compile(stringPattern, Pattern.CASE_INSENSITIVE);
				m2 = p2.matcher(string);
				Pattern p3 = Pattern.compile(nullPattern, Pattern.CASE_INSENSITIVE);
				Matcher m3 = p3.matcher(string);
				if (!m.matches() && !m2.matches() && !m3.matches()) {
					System.out.println("Error: You have some syntax error near the " + string + ", please try again");
				}

			}
		} else if (m2.matches()) {
			// 类似于insert into table values(...,仅判断输入内容即可
			String value = splits[1].replaceAll("\\s", "");
			String[] values = value.split(",");
			if (values.length != Utils.getColumns(tableName, Use.getInstance().getDatabaseUsed()).size()) {
				System.out.println("ERROR: Column count doesn't match value count at row 1");
			}
			for (String string : values) {
				// 判断每个value
				String numberOnlyPattern = "^[0-9]*$";
				String stringPattern = "\\'.*\\'";
				String nullPattern = "null";
				// null
				p = Pattern.compile(numberOnlyPattern, Pattern.CASE_INSENSITIVE);
				m = p.matcher(string);
				p2 = Pattern.compile(stringPattern, Pattern.CASE_INSENSITIVE);
				m2 = p2.matcher(string);
				Pattern p3 = Pattern.compile(nullPattern, Pattern.CASE_INSENSITIVE);
				Matcher m3 = p3.matcher(string);
				if (!m.matches() && !m2.matches() && !m3.matches()) {
					System.out.println("Error: You have some syntax error near the " + string + ", please try again");
				}

			}
		} else {
			System.out.println("Error: You have some syntax error, please try again");
			return false;
		}
		return true;
	}
}
